Automated physical design tuning involves a database management system (DBMS) recommending a set of physical structures that increase the performance of an underlying database. Physical design has been formulated as a problem statement, traditionally: Given a workload W and a storage budget B, find the set of physical structures, or configuration, that fits in B and results in the lowest execution cost for W. Most modern commercial DBMS's have some facilities for automated design tuning. In general, however, it has not been possible to include in the tuning process information beyond the basic information of the design tuning problem statement.
For instance, it has not been possible to tune a given workload for maximum performance under a storage constraint while at the same time ensuring that no query degrades by more than 10% with respect to the original configuration. As another example, it has not been possible to enforce that the clustered index on a table T cannot be defined over certain columns of T that would introduce hot-spots (without specifying which of the remaining columns should be chosen). As yet another example, in order to decrease contention during query processing, there is no way to avoid any single column from a table from appearing in more than, say, three indexes (the more indexes a column appears in, the more contention arises due to exclusive locks during updates). While some new approaches allow more flexibility in the specification of a physical design tuning problem, existing solutions require that the whole specification to be provided upfront, without possibility of interaction.
Described herein are techniques for flexible and interactive physical design tuning.